-- @owner: cr13
-- @date: 2025/3/27
-- @testpoint:同名参数类型不同的重载函数，删除并创建函数，部分合理报错
--step1: 创建函数，2个入参为int类型;expect:成功
drop FUNCTION if exists func_ddl0023(a int,b int);
CREATE FUNCTION func_ddl0023(a int, b int)
RETURNS int
AS $$
declare
sum int;
BEGIN
select a + b into sum;
return sum;
END;
$$
LANGUAGE plpgsql;
/
--step2: 创建函数入参为text类型;expect:成功
drop FUNCTION if exists func_ddl0023(a text,b text);
CREATE FUNCTION func_ddl0023(a text, b text)
RETURNS int
AS $$
declare
sum int;
BEGIN
select a + b into sum;
return sum;
END;
$$
LANGUAGE plpgsql;
/
--step3: 创建视图;expect:成功
drop view if exists v_ddl0023;
create view v_ddl0023 as select func_ddl0023(1,2);
--step4: 查询视图;expect:成功
select * from v_ddl0023;
--step5: 删除视图引用的自定义函数;expect:成功
drop FUNCTION func_ddl0023(int,int);
--step6: 查询视图状态;expect:无效
select valid from pg_object where object_oid='v_ddl0023'::regclass;
--step7: 查询视图数据;expect:成功
select * from v_ddl0023;
--step8: 查询视图状态;expect:有效（使用重载函数恢复）
select valid from pg_object where object_oid='v_ddl0023'::regclass;
--step9: 查询视图定义;expect:成功
select pg_get_viewdef('v_ddl0023');
--step10: 删除重载函数;expect:成功
drop FUNCTION func_ddl0023(text,text);
--step11: 查询视图状态;expect:无效
select valid from pg_object where object_oid='v_ddl0023'::regclass;
--step12: 查询视图数据;expect:报错
select * from v_ddl0023;
--step13: 查询视图定义;expect:有告警
select pg_get_viewdef('v_ddl0023');
--step14: 重建视图依赖的函数;expect:成功
CREATE FUNCTION func_ddl0023(a int, b int)
RETURNS int
AS $$
declare
sum int;
BEGIN
select a + b into sum;
return sum;
END;
$$
LANGUAGE plpgsql;
/
--step15: 查询视图状态;expect:无效
select valid from pg_object where object_oid='v_ddl0023'::regclass;
--step16: 查询视图数据;expect:成功
select * from v_ddl0023;
--step17: 查询视图定义;expect:成功
select pg_get_viewdef('v_ddl0023');
--step18: 查询视图状态;expect:有效
select valid from pg_object where object_oid='v_ddl0023'::regclass;
--step19: 清理环境;expect:成功
drop function func_ddl0023(a int,b int);
drop view v_ddl0023;
